package smb.service;

import lombok.AllArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import smb.dao.StudentDao;
import smb.domain.Student;
import smb.mapper.StudentMapper;
import smb.vo.*;

import javax.persistence.EntityNotFoundException;
import java.util.List;
import java.util.Optional;

@Service
@AllArgsConstructor
public class AdminService {
    private JdbcTemplate jdbcTemplate;
    private StudentDao studentDao;
    private StudentMapper studentMapper;

    public StudentPage getStudents(int pageSize, int pageNum) {
        Sort sort = Sort.by(Sort.Direction.ASC, "id");
        Page<Student> studentPage = studentDao.findAll(PageRequest.of(pageNum - 1, pageSize, sort));
        return new StudentPage(studentPage.getTotalElements(), studentPage.getTotalPages(), studentMapper.studentsToStudentVos(studentPage.toList()));
    }

    public void postStudent(StudentVo studentVo) {
        Optional<Student> student = studentDao.findById(studentVo.getId());
        if (student.isPresent()) {
            studentMapper.updateStudent(studentVo, student.get());
            studentDao.save(student.get());
        } else {
            throw new EntityNotFoundException();
        }
    }

    public void putStudent(StudentVo studentVo) {
        Student student = studentMapper.studentVoToStudent(studentVo);
        studentDao.save(student);
    }

    public void deleteStudent(String id) {
        studentDao.deleteById(id);
    }

    public StudentRankPage getRank(int pageSize, int pageNum) {
        String sql = "SELECT\n" +
                "    AVG(score.total) as avg_total,\n" +
                "    student.id, student.name, student.sex\n" +
                "FROM score\n" +
                "JOIN student ON score.stu_id = student.id\n" +
                "GROUP BY student.id\n" +
                "ORDER BY avg_total DESC\n" +
                "LIMIT ?, ?;";
        if (pageNum < 1) {
            throw new EntityNotFoundException("Page index must not be less than zero!");
        }
        List<StudentGPA> studentGPAs = jdbcTemplate.query(sql, new Object[] {(pageNum - 1) * pageSize , pageSize}, ((resultSet, i) -> {
            double GPA = resultSet.getDouble(1);
            StudentVo student = new StudentVo(resultSet.getString(2), resultSet.getString(3), resultSet.getString(4));
            return new StudentGPA(student, GPA);
        }));
        long totalElements = studentDao.count();
        int totalPages;
        if (totalElements % pageSize == 0) {
            totalPages = (int) (totalElements / pageSize);
        } else {
            totalPages = (int) (totalElements / pageSize) + 1;
        }
        return new StudentRankPage(totalElements, totalPages, studentGPAs);
    }
}
